﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using BackendDeveloper_WS.Models;
using BackendDeveloper_WS.Persistence;
using System.Data.Common;
using System.Data.SqlClient;



namespace BackendDeveloper_WS.Services
{
    public class OrderServices
    {
        private SqlDataManager sqldm;
        private SqlConnection sqlcon;
        
        /// <summary>
        /// Creates a new instance of OrderServices
        /// </summary>
        public OrderServices()
        {
            sqldm = new SqlDataManager();
            sqlcon = sqldm.createConnection();
            
        }

        /// <summary>
        /// Gets the List of tha saved Work Orders 
        /// </summary>
        /// <returns>List of WorkOrder objects</returns>
        public IEnumerable<WorkOrder> getWorkOrdersList()
        {
            ///SqlConnection sqlcon = sqldm.createConnection();
            SqlDataReader dreader = sqldm.executeQuery("SELECT * FROM WorkOrders", sqlcon);
            List<WorkOrder> woList = new List<WorkOrder>();
            while (dreader.Read())
            {
                WorkOrder wo = new WorkOrder((int)dreader["Id"], (string)dreader["Description"], (string)dreader["Address"], (DateTime)dreader["CreationDate"], (Double)dreader["Longitude"], (Double)dreader["Latitude"]);
                woList.Add(wo);
            }
            ///sqldm.closeConnection(sqlcon);
            return woList;
        }

        /// <summary>
        /// Gets the list of the saved WorkOrders near to the given latitude and longitude
        /// </summary>
        /// <param name="lat">reference latitude</param>
        /// <param name="lon">reference longitude</param>
        /// <returns>List of WorkOrders near to the given latitude and longitude</returns>
        public IEnumerable<WorkOrder> getWorkOrdersList(double lat, double lon)
        {
            ///SqlConnection sqlcon = sqldm.createConnection();
            SqlDataReader dreader = sqldm.executeQuery("SELECT * FROM WorkOrders WHERE FLOOR(Latitude)=FLOOR("+lat+") AND FLOOR(Longitude)=FLOOR("+lon+")", sqlcon); 
            List<WorkOrder> woList = new List<WorkOrder>();
            while (dreader.Read())
            {
                WorkOrder wo = new WorkOrder((int)dreader["Id"], (string)dreader["Description"], (string)dreader["Address"], (DateTime)dreader["CreationDate"], (Double)dreader["Longitude"], (Double)dreader["Latitude"]);
                woList.Add(wo);
            }
            ///sqldm.closeConnection(sqlcon);
            return woList;
        }

        /// <summary>
        /// Cretaes and save a new WorkOrder
        /// </summary>
        /// <param name="description">description of the WorkOrder</param>
        /// <param name="address">address of the WorkOrder</param>
        /// <param name="latitude">latitude of the WorkOrder</param>
        /// <param name="longitude">longitude of the WorkOrder</param>
        /// <returns>True if the WorkOrder is created, False if it's not</returns>
        public bool createNewWorkOrder(string description, string address,  Double latitude, Double longitude) 
        {
            bool success=true;
            try
            {
                sqldm.executeQuery("INSERT INTO WorkOrders(Description, Address, CreationDate, Longitude, Latitude) VALUES('" + description + "', '" + address + "', CURRENT_TIMESTAMP," + latitude + ", " + longitude + ") ", sqlcon);
            }catch(Exception e){
                success = false;
            }
            return success;
           
        }

        /// <summary>
        /// Gets a WorkOrder according to the given id
        /// </summary>
        /// <param name="id">id of the WorkOrder</param>
        /// <returns>WorkOrder</returns>
        public WorkOrder getWorkOrder(int id)
        {
            SqlDataReader dreader = sqldm.executeQuery("SELECT * FROM WorkOrders WHERE id="+id, sqlcon); 
            WorkOrder wo = null;
            while (dreader.Read())
            {
                wo = new WorkOrder((int)dreader["Id"], (string)dreader["Description"], (string)dreader["Address"], (DateTime)dreader["CreationDate"], (Double)dreader["Longitude"], (Double)dreader["Latitude"]);
               
            }
            return wo;
        }

        /// <summary>
        /// Edits the data of a WorkOrder
        /// </summary>
        /// <param name="id">id of the WorkOrder to be edited</param>
        /// <param name="description">description for the WorkOrder to be edited</param>
        /// <param name="address">address for the WorkOrder to be edited</param>
        /// <param name="creationDate">creation date for the WorkOrder to be edited</param>
        /// <param name="latitude">latitude for the WorkOrder to be edited</param>
        /// <param name="longitude">longitude for the WorkOrder to be edited</param>
        /// <returns>True if the workOrder is updated, False if it's not</returns>
        public bool editWorkOrder(int id, string description, string address, double latitude, double longitude)
        {
            bool success = true;
            try{
                 sqldm.executeQuery("UPDATE WorkOrders SET Description='"+description+"', Address='"+address+"', Latitude= "+latitude+", Longitude="+longitude+" WHERE id=" + id, sqlcon);
            }catch(Exception e){
                success = false;
            }
            return success;
        }

        /// <summary>
        /// Gets the comments of a WorkOrder
        /// </summary>
        /// <param name="workOrderId">id of the WorkOrder related to the comments</param>
        /// <returns>List of Comment objects relted to the idicated WorkOrder</returns>
        public IEnumerable<Comment> getComments(int workOrderId)
        {
            SqlDataReader dreader = sqldm.executeQuery("SELECT * FROM Comments WHERE WorkOrders_id="+workOrderId, sqlcon); 
            List<Comment> coList = new List<Comment>();
            while (dreader.Read())
            {
                Comment co = new Comment((int)dreader["Id"], (string)dreader["Message"], (DateTime)dreader["CreationDate"] ,workOrderId);
                coList.Add(co);
            }
            return coList;
        }

        /// <summary>
        /// Creates a new Comment
        /// </summary>
        /// <param name="workOrderId">Id of the related WorkOrder</param>
        /// <param name="message">Message of the comment</param>
        /// <returns>True if the comment is created, False if it's not</returns>
        public bool createNewComment(int workOrderId, string message)
        {
            bool success = true;
            try
            {
                sqldm.executeQuery("INSERT INTO Comments(Message, CreationDate, WorkOrders_id) VALUES('" + message + "', CURRENT_TIMESTAMP, " + workOrderId + ") ", sqlcon); 
            }
            catch (Exception e)
            {
                success = false;
            }
            return success;
        }
    }
}